﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ScrewTurn.Wiki;
using ScrewTurn.Wiki.PluginFramework;
using System.Diagnostics;

using UniBasel.Biozentrum.NMR;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Configuration;

public partial class MySpectrometers : BasePage
{
	protected void Page_Load(object sender, EventArgs e)
	{
		#region Verify Authentication
		// Re-verify authentication
		PageInfo pageInfo = Pages.Instance.FindPage(Request["Page"]);
		if (pageInfo == null)
		{
			// Page creation
			if (SessionFacade.LoginKey == null && !Settings.PublicAccess)
			{
				Response.Redirect("Login.aspx?Redirect=MySpectrometers.aspx?Page=" + Request["Page"]);
			}
			if (!Settings.UsersCanCreateNewPages && !SessionFacade.Admin)
			{
				Response.Redirect("AccessDenied.aspx");
			}
		}
		else
		{
			// Page modification
			if (pageInfo.Provider.ReadOnly) Response.Redirect(pageInfo.Name + Settings.PageExtension);

			if (pageInfo.Status == PageStatus.Locked)
			{
				if (SessionFacade.LoginKey == null || (SessionFacade.LoginKey != null && (SessionFacade.LoginKey == null || !SessionFacade.Admin)))
				{
					Response.Redirect("AccessDenied.aspx");
				}
			}

			// Login/Authorization check
			if (SessionFacade.LoginKey == null && !Settings.PublicAccess && pageInfo.Status != PageStatus.Public)
			{
				Response.Redirect("Login.aspx?Redirect=" + Tools.UrlEncode(Request.Url.ToString().Substring(Request.Url.ToString().ToLowerInvariant().IndexOf("MySpectrometers.aspx"))));
			}
			if ((Settings.PublicAccess || pageInfo.Status == PageStatus.Public) && SessionFacade.LoginKey == null)
			{
				//lblIpLogged.Visible = true;
			}
		}
		#endregion

		SpectrometersDataSource.SelectParameters["username"].DefaultValue = SessionFacade.Username;
		PrevVersionsSQLDatasource.SelectParameters["username"].DefaultValue = SessionFacade.Username;

		if (Request["SpecID"] != null)
		{
			SpectrometersTools.ActiveViewIndex = 2;
			showSpecInfowithID(Request["SpecID"]);
		}

	}

	protected void AddSpectrometerButton_Click(object sender, EventArgs e)
	{
		SpectrometersTools.ActiveViewIndex = 1;
		UniqueNamecv.Enabled = true;
		tbName.ReadOnly = false;
		butSubmit.CommandName = "Add";
		UpdateInfopn.Style.Add("display", "none");

		lblTitle.Text = "Add New Spectrometer";


		NMRGenUtils.checkUserCredentials(SessionFacade.Username);
	}

	protected void butClear_Click(object sender, EventArgs e)
	{
		tbName.Text = "";
		tbMemo.Text = "";
		tbContactEmail.Text = "";
		tbContactPerson.Text = "";
		tbLocation.Text = "";
		tbContent.Text = "";
		tbVersionInfo.Text = "";
	}

	protected void ValidateForScripts(object source, ServerValidateEventArgs args)
	{
		args.IsValid = !Regex.IsMatch(args.Value.ToString(), @"\<script.*?\>");
	}

	protected void UniqueNameValidate(object source, ServerValidateEventArgs args)
	{
		SqlConnection MyConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NMRUserDataConnectionString"].ConnectionString);
        MyConnection.Open();
        SqlCommand checkSpectrometerNameSELECTQuery = MyConnection.CreateCommand();
        string QueryText =
            @"SELECT 
				name 
			  FROM 
				spectrometers s, 
				relation_spectrometers_to_users rsu 
			  WHERE
				s.id=rsu.spectrometerid AND rsu.username='##username##' AND s.name='##specname##'
			";
        checkSpectrometerNameSELECTQuery.CommandText = QueryText.Replace("##username##", SessionFacade.Username).Replace("##specname##", tbName.Text);
        SqlDataReader myReader = checkSpectrometerNameSELECTQuery.ExecuteReader();

        if (myReader.HasRows) args.IsValid = false;
        else args.IsValid = true;
        myReader.Close();
        MyConnection.Close();
	}

	protected void SpectrometersTableGv_RowCommand(object sender, GridViewCommandEventArgs e)
	{
		string currentCommand = e.CommandName;

		if (currentCommand != "showSelectedSpectrometerInfo") return;

		SpectrometersTools.ActiveViewIndex = 2;

		int currentRowIndex = Int32.Parse(e.CommandArgument.ToString());

		string SpecID = SpectrometersTableGv.DataKeys[currentRowIndex].Value.ToString();

		showSpecInfowithID(SpecID);
	}

	protected void VersionLinkButton_Click(object sender, CommandEventArgs e)
	{
		string currentCommand = e.CommandName;
		if (currentCommand != "ShowSpecWithID") return;

		SpectrometersTools.ActiveViewIndex = 2;

		string SpecID =	 e.CommandArgument.ToString();

		showSpecInfowithID(SpecID);
	}

	protected void showSpecInfowithID(string SpecID)
	{
		SqlConnection MyConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NMRUserDataConnectionString"].ConnectionString);
		MyConnection.Open();

		SqlCommand getSpectrometerInfoQuery = MyConnection.CreateCommand();

		getSpectrometerInfoQuery.CommandText = @"
SELECT
	name AS Name,
	[date] AS [Date],
	location AS Location,
	memo AS [Short Description],
	contact AS Contact,
	contactmail AS [ContactEmail],
	content AS content,
	version AS Version
FROM 
	spectrometers s,
	relation_spectrometers_to_users rsu
WHERE id=##id##	AND rsu.username='##username##' AND rsu.spectrometerid=s.id
	".Replace("##id##", SpecID).Replace("##username##", SessionFacade.Username);

		SqlDataReader myReader = getSpectrometerInfoQuery.ExecuteReader();

		if (!myReader.HasRows)
		{
			Log.LogEntry("Show Spectrometer with ID=" + SpecID + ". SqlQuery returned no rows.", EntryType.Error, SessionFacade.Username);
			throw (new ArgumentOutOfRangeException("SpecID", "There is no experiment with ID=" + SpecID + "."));
		}
		while (myReader.Read())
		{
			lblTitle.Text = "<b>" + myReader["Name"] + "</b> spectrometer.";
			lblMemo2.Text = myReader["Short Description"].ToString();
			lblDate2.Text = "Version <b>" + myReader["Version"].ToString() + "</b> from " + myReader["Date"].ToString() + ".";
			lblLocation2.Text = "Location: " + myReader["Location"].ToString() + ".";
			lblContact2.Text = @"Responsible person: <a href=""mailto://" + myReader["ContactEmail"].ToString() + @""">" + myReader["Contact"].ToString() + "</a>.";
			tbContent2.Text = myReader["Content"].ToString();
			btnRealyDelete2.CommandArgument = SpecID;
			btnUpdate2.CommandArgument = SpecID;
		}

		myReader.Close();
		MyConnection.Close();

		PrevVersionsSQLDatasource.SelectParameters["id"].DefaultValue = SpecID.ToString();
		PrevVersionsSQLDatasource.DataBind();
	}

	protected void btnDelete2_Click(object sender, EventArgs e)
	{
		btnDelete2.Enabled = false;
		btnCancelDelete2.Visible = true;
		btnRealyDelete2.Visible = true;
		lblDeleteQuestion.Visible = true;
	}
	
	protected void btnCancelDelete2_Click(object sender, EventArgs e)
	{
		btnDelete2.Enabled = true;
		btnRealyDelete2.Visible = false;
		btnCancelDelete2.Visible = false;
		lblDeleteQuestion.Visible = false;
	}

	protected void btnRealyDelete2_Command(object sender, CommandEventArgs e)
	{
		string SpecID = e.CommandArgument.ToString();

		SqlConnection MyConnection = new SqlConnection(Constants.NMRUserDataConStr);
		MyConnection.Open();

		SqlCommand deleteSpectrometerInfoQuery = MyConnection.CreateCommand();
		deleteSpectrometerInfoQuery.CommandText = "DELETE FROM relation_spectrometers_to_users WHERE spectrometerid=##specid##".Replace("##specid##", SpecID);
		SqlDataReader MyReader = deleteSpectrometerInfoQuery.ExecuteReader();
		MyReader.Close();

		deleteSpectrometerInfoQuery.CommandText = "DELETE FROM spectrometers WHERE id=##specid##".Replace("##specid##", SpecID);
		MyReader = deleteSpectrometerInfoQuery.ExecuteReader();
		MyReader.Close();

		MyConnection.Close();
		btnCancelDelete2_Click(sender, e);

		SpectrometersTools.ActiveViewIndex = 0;
		SpectrometersTableGv.DataBind();
	}

	protected void btnUpdate2_Command(object sender, CommandEventArgs e)
	{
		SpectrometersTools.ActiveViewIndex = 1;
		UniqueNamecv.Enabled = false;

		butSubmit.CommandName = "Update";
		UpdateInfopn.Style.Add("display", "inline");

		string SpecID = e.CommandArgument.ToString();
		SqlConnection MyConnection = new SqlConnection(Constants.NMRUserDataConStr);
		MyConnection.Open();

		SqlCommand getSpectrometerInfoQuery = MyConnection.CreateCommand();

		getSpectrometerInfoQuery.CommandText = @"
SELECT
	name AS Name,
	[date] AS [Date],
	location AS Location,
	memo AS [Short Description],
	contact AS Contact,
	contactmail AS [ContactEmail],
	content AS content,
	version AS Version
FROM spectrometers
WHERE id=##id##
	".Replace("##id##", SpecID);

		SqlDataReader myReader = getSpectrometerInfoQuery.ExecuteReader();

		if (!myReader.HasRows)
		{
			Log.LogEntry("Show Spectrometer with ID=" + SpecID + ". SqlQuery returned no rows.", EntryType.Error, SessionFacade.Username);
			throw (new ArgumentOutOfRangeException("SpecID", "There is no experiment with ID=" + SpecID + "."));
		}
		while (myReader.Read())
		{
			lblTitle.Text = "Change of Spectrometer " + myReader["Name"];
			tbName.Text = myReader["Name"].ToString();
			tbMemo.Text = myReader["Short Description"].ToString();
			tbLocation.Text = myReader["Location"].ToString();
			tbContactPerson.Text = myReader["Contact"].ToString();
			tbContactEmail.Text = myReader["ContactEmail"].ToString();
			tbContent.Text = myReader["Content"].ToString();
			btnRealyDelete2.CommandArgument = SpecID;
			btnUpdate2.CommandArgument = SpecID;
			butSubmit.CommandArgument = myReader["Version"].ToString();
		}
		tbName.ReadOnly = true;

		myReader.Close();
		MyConnection.Close();
	}

	protected void butSubmit_Command(object sender, CommandEventArgs e)
	{
		if (!Page.IsValid) return;

		SqlConnection MyConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NMRUserDataConnectionString"].ConnectionString);
		MyConnection.Open();

		SqlCommand NewSpectrometerINSERTQuery = MyConnection.CreateCommand();

		string CommandText = @"INSERT INTO spectrometers
(name, memo,location, contact, contactmail, content, version, versioninfo) 
VALUES (N'##name##', N'##memo##', N'##location##', N'##contact##', N'##contactmail##', N'##content##', N'##version##', N'##versioninfo##')";
		string LogMsg;


		switch (e.CommandName.ToString())
		{
			case "Add":
				CommandText = CommandText.Replace("##version##", "1").Replace("##versioninfo##", "As it was before me");
				LogMsg = "New spectrometer added."; ;
				break;
			case "Update":
				CommandText = CommandText.Replace("##version##", (getLastVersion(tbName.Text) + 1).ToString());
				CommandText = CommandText.Replace("##versioninfo##", tbVersionInfo.Text);
				LogMsg = "Spectrometer with ID " + e.CommandArgument.ToString() + " updated.";
				tbName.ReadOnly = false;
				break;
			default:
				throw (new ArgumentException("Only Add or Update values are possible."));
		}
		CommandText = CommandText.Replace("##name##", tbName.Text).Replace("##memo##", tbMemo.Text).
								  Replace("##content##", tbContent.Text.Replace(@"'", @"\'")).Replace("##contact##", tbContactPerson.Text).
								  Replace("##contactmail##", tbContactEmail.Text).Replace("##location##", tbLocation.Text);

		NewSpectrometerINSERTQuery.CommandText = CommandText;
		SqlDataReader myReader = NewSpectrometerINSERTQuery.ExecuteReader();
		myReader.Close();
		
		SqlCommand getLastInsertedID = MyConnection.CreateCommand();
		getLastInsertedID.CommandText = @"SELECT @@IDENTITY";

		long SpecID = Convert.ToInt32(getLastInsertedID.ExecuteScalar());

		Log.LogEntry(LogMsg, EntryType.General, SessionFacade.Username);

		string un = NMRGenUtils.checkUserCredentials(SessionFacade.Username);

		myReader.Close();

		NewSpectrometerINSERTQuery.CommandText = @"
INSERT INTO relation_spectrometers_to_users
VALUES ('##username##', ##spectrometerid##);
".Replace("##username##", un).Replace("##spectrometerid##", SpecID.ToString());
		myReader = NewSpectrometerINSERTQuery.ExecuteReader();

		MyConnection.Close();
		SpectrometersTools.ActiveViewIndex = 0;
		butClear_Click(sender, e);
		SpectrometersTableGv.DataBind();
	}

	private uint getLastVersion(string SpecName)
	{
		uint result;
		SqlConnection MyConnection = new SqlConnection(Constants.NMRUserDataConStr);
		MyConnection.Open();

		SqlCommand getLastVersionSELECTQuery = MyConnection.CreateCommand();
		getLastVersionSELECTQuery.CommandText = @"
SELECT
  MAX(version) AS MaxVersion
FROM
  spectrometers s,
  relation_spectrometers_to_users rsu
WHERE
  rsu.username='##username##' AND rsu.spectrometerid=s.id AND s.name='##name##'
".Replace("##username##", SessionFacade.Username).Replace("##name##", SpecName);
		SqlDataReader myReader = getLastVersionSELECTQuery.ExecuteReader();
		if (!myReader.HasRows) result = 0;
		else
		{
		    result = 0;
		    while(myReader.Read()) result = uint.Parse(myReader["MaxVersion"].ToString());
		}

		MyConnection.Close();
		return result;
	}
}
